
USE [BS2]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 07/29/2019 12:58:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------------------------------*/
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, ISNULL(dbo.Voucher.SVoucherNo, 0) AS SVoucherNo, dbo.Voucher.SessionID, 
                      dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, 
                      dbo.Voucher.ReadOnly, dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, Accounts_4.AccountTitle, Accounts_4.AccountTitleUrdu, 
                      dbo.VoucherDetails.Remarks, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, 
                      dbo.VoucherDetails.DrOrCr, Accounts_4.ParentAccount, dbo.Voucher.BankAccountID, Accounts_1.AccountTitle AS BankAccountTitle, 
                      Accounts_1.AccountTitleUrdu AS BankAccountTitleUrdu, Accounts_1.Remarks AS BankAccountAddress, 
                      Accounts_1.CellNo + '  ' + Accounts_1.Phone AS BankAccountContactNo, Accounts_1.LiscNo, Accounts_1.LiscDate, dbo.Voucher.SaleTax, dbo.Voucher.Expense, 
                      dbo.Voucher.Discount, dbo.Voucher.InvDiscPercent, dbo.Voucher.InvDiscount, dbo.Voucher.Cash, dbo.Voucher.AddLess, dbo.Voucher.PrvBalance, 
                      dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, dbo.VoucherDetails.Amount AS ActualAmount, 
                      dbo.Voucher.ModifyID, Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.Voucher.CustomerName, 
                      dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, dbo.vw_Items.Type, dbo.VoucherDetails.ItemID, 
                      dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.vw_Items.PRate, dbo.vw_Items.SRate, 
                      dbo.vw_Items.CompanyID, dbo.vw_Items.CompanyCode, dbo.vw_Items.CompanyName, dbo.vw_Items.CompanyNameUrdu, dbo.vw_Items.RackID, 
                      dbo.vw_Items.RackCode, dbo.vw_Items.RackName, dbo.vw_Items.RackNameUrdu, dbo.VoucherDetails.BatchNo, dbo.VoucherDetails.ExpiryDate, 
                      dbo.VoucherDetails.Packs, dbo.VoucherDetails.Lose, dbo.VoucherDetails.PSize, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Bonus, dbo.VoucherDetails.PackRate, 
                      dbo.VoucherDetails.Rate, dbo.VoucherDetails.STaxPercent, dbo.VoucherDetails.STaxAmount, dbo.VoucherDetails.DiscPercent, dbo.VoucherDetails.DiscAmount, 
                      dbo.VoucherDetails.NetAmount, dbo.Voucher.BookedByID, Accounts_2.AccountTitle AS BookedByName, Accounts_2.AccountTitleUrdu AS BookedByNameUrdu, 
                      Accounts_2.CellNo AS BookedByCellNo, Accounts_2.Phone AS BookedByPhone, Accounts_2.Remarks AS BookedByAddress, 
                      Accounts_2.ComPercent AS BookedByComPercent, dbo.Voucher.DeliveredByID, Accounts_3.AccountTitle AS DeliveredByName, 
                      Accounts_3.AccountTitleUrdu AS DeliveredByNameUrdu, Accounts_3.CellNo AS DeliveredByCellNo, Accounts_3.Phone AS DeliveredByPhone, 
                      Accounts_3.Remarks AS DeliveredByAddress, Accounts_3.ComPercent AS DeliveredByComPercent, Accounts_1.AreaID, dbo.Area.AreaCode, dbo.Area.AreaName, 
                      dbo.Area.AreaNameUrdu, dbo.Area.RegionID, dbo.Region.RegionCode, dbo.Region.RegionName, dbo.Region.RegionNameUrdu, dbo.Voucher.Amount AS AmountM, 
                      dbo.Voucher.TotalAmount AS TotalAmountM, dbo.Voucher.NetAmount AS NetAmountM, dbo.Voucher.BillNo, dbo.Voucher.TransID, 
                      dbo.Accounts.AccountTitle AS TransName, dbo.Accounts.AccountTitleUrdu AS TransNameUrdu, dbo.Accounts.Remarks AS TransAddress, 
                      dbo.Accounts.CellNo AS TransCellNo, dbo.Accounts.Phone AS TransPhone, dbo.Voucher.FatherName, dbo.Combos.ComboName
FROM         dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.Accounts RIGHT OUTER JOIN
                      dbo.Combos RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Combos.ComboID = dbo.Voucher.TransID ON dbo.Accounts.AccountNo = dbo.Voucher.TransID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_3 ON dbo.Voucher.DeliveredByID = Accounts_3.AccountNo ON Accounts_2.AccountNo = dbo.Voucher.BookedByID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 LEFT OUTER JOIN
                      dbo.Region RIGHT OUTER JOIN
                      dbo.Area ON dbo.Region.RegionID = dbo.Area.RegionID ON Accounts_1.AreaID = dbo.Area.AreaID ON 
                      dbo.Voucher.BankAccountID = Accounts_1.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON dbo.Voucher.ModifyID = Employees_1.EmployeeID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID RIGHT OUTER JOIN
                      dbo.vw_Items RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.vw_Items.ItemID = dbo.VoucherDetails.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_4 ON dbo.VoucherDetails.AccountNo = Accounts_4.AccountNo ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
